package com.aizu.service;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import com.aizu.entity.User;
import com.isp.jdbc.model.Pager;
import com.isp.utils.JdbcUtils;

@Service
public class MainService extends MasterService {

	private final static String TABLE_RENT_MAIN = "t_rent_main";
	private final static String TABLE_RENT_CLASS = "t_rent_class";

	private User convertUser(List<Map<String, Object>> masterList) {
		if (null != masterList && masterList.size() > 0) {
			Map<String, Object> masterMap = masterList.get(0);
			User master = new User();
			master.setUserId((Integer) masterMap.get("id"));
			master.setUserName((String) masterMap.get("name"));
			master.setUserPhone((String) masterMap.get("phone"));
			master.setUserType(User.USER_TYPE_MASTER);
			master.setFromUserName((String) masterMap.get("wx_userid"));
			// master.setLastLoginDate(null);
			return master;
		}
		return null;
	}

	/**
	 * 获取用微信用户关联的手机号
	 * 
	 * @param fromUserName
	 * @return
	 * @throws SQLException
	 */
	public String findPhone(String fromUserName) throws SQLException {
		Map<String, Object> only = this.findOnly("SELECT rm.phone FROM t_rent_main rm WHERE rm.wx_userid = ?",
				new Object[] { fromUserName });
		if (null != only)
			return (String) only.get("phone");
		return null;
	}

	/**
	 * 注册
	 * 
	 * @param map
	 * @return
	 */
	public User saveUser(Map<String, Object> map) throws Exception {
		Map<String, Object> createSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_INSERT, TABLE_RENT_MAIN, map,
				null);
		boolean execute = this.execute((String) createSqlParam.get(JdbcUtils.SQL),
				(Object[]) createSqlParam.get(JdbcUtils.PARAM));
		User master = null;
		if (execute) {
			// map.remove("create_date");
			// map.remove("mod_date");
			List<Map<String, Object>> masterList = findMaster(map);
			master = convertUser(masterList);
		}
		return master;
	}

	/**
	 * 绑定微信id
	 * 
	 * @param map
	 * @return
	 */
	public boolean updateUser(User user) throws Exception {
		return this.execute("update t_rent_main set wx_userid = ? where id = ?", new Object[] { user.getFromUserName(),
				user.getUserId() });
	}

	/**
	 * 登陆
	 * 
	 * @param map
	 * @return
	 */
	public User loadUser(Map<String, Object> map) throws Exception {
		List<Map<String, Object>> masterList = findMaster(map);
		return convertUser(masterList);
	}

	/**
	 * 查找屋主
	 * 
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>> findMaster(Map<String, Object> map) throws Exception {
		Map<String, Object> querySqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_QUERY, TABLE_RENT_MAIN, null,
				map);
		return this.find((String) querySqlParam.get(JdbcUtils.SQL), (Object[]) querySqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 新增小区
	 * 
	 * @param map
	 * @return
	 */
	public Map<String, Object> saveCommunity(Map<String, Object> map) throws Exception {
		Map<String, Object> community = findCommunity(map);
		if (null != community)
			return community;

		map.put("create_date", new Date());
		Map<String, Object> createSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_INSERT, "t_community", map,
				null);
		boolean execute = this.execute((String) createSqlParam.get(JdbcUtils.SQL),
				(Object[]) createSqlParam.get(JdbcUtils.PARAM));
		if (execute) {
			map.remove("create_date");
			community = findCommunity(map);
		}
		return community;
	}

	/**
	 * 新增小区，同时增加小区房主关系
	 * 
	 * @param map
	 * @return
	 */
	public Map<String, Object> saveCommunity(Map<String, Object> map, String userId) throws Exception {
		Map<String, Object> community = saveCommunity(map);
		// 如果用户id不为空，同时保存用户与小区的关系表，表明此用户可以在此小区内新增房子
		if (null != community && StringUtils.isNotBlank(userId)) {
			this.execute("insert into t_tmp_rent_community (community_id, main_id) values (?, ?)", new Object[] {
					community.get("id"), userId });
		}
		return community;
	}

	/**
	 * 查找小区
	 * 
	 * @param map
	 * @return
	 */
	public Map<String, Object> findCommunity(Map<String, Object> map) throws Exception {
		Map<String, Object> querySqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_QUERY, "t_community", null, map);
		return this.findOnly((String) querySqlParam.get(JdbcUtils.SQL), (Object[]) querySqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 查找屋主的房子
	 * 
	 * @param mainId
	 * @return
	 */
	public List<Map<String, Object>> findMyCommunity(int mainId) throws Exception {
		return this
				.find("SELECT d.name dname, c.* FROM t_community c, t_department d WHERE c.dept_id = d.id"
						+ " AND (EXISTS (SELECT 1 FROM t_rent_room r WHERE c.id = r.community_id AND r.main_id = ?)"
						+ " OR EXISTS (SELECT 1 FROM t_tmp_rent_community trc WHERE c.id = trc.community_id AND trc.main_id = ?))",
						new Object[] { mainId, mainId });
	}

	/**
	 * 查找房主下的房屋
	 * 
	 * @param userId 
	 * @return
	 */
	public List<Map<String, Object>> findHouseList(int userId) throws Exception {
		return find(
				"SELECT r.*, c.name communityname, d.name departmentname FROM t_rent_room r, t_community c, t_department d"
						+ " WHERE r.community_id = c.id and r.dept_id = d.id and r.main_id = ?",
				new Object[] { userId });
	}
	
	/**
	 * 查找房主下的房屋
	 * 
	 * @param houseId 
	 * @return
	 */
	public Map<String, Object> findHouse(int houseId) throws Exception {
		return findOnly(
				"SELECT r.*, c.name communityname, d.name departmentname FROM t_rent_room r, t_community c, t_department d"
						+ " WHERE r.community_id = c.id and r.dept_id = d.id and r.id = ?",
						new Object[] { houseId });
	}
	
	/**
	 * 查找房屋下的房间
	 * 
	 * @param userId 
	 * @return
	 */
	public List<Map<String, Object>> findHouseRoom(int houseId) throws Exception {
		return find("SELECT r.*, (SELECT COUNT(1) FROM t_rent_bed rb WHERE rb.class_id = r.id) bed_count,"
				+ " (SELECT COUNT(1) FROM t_rent_bed rb WHERE rb.class_id = r.id AND rb.grant_id IS NULL) bed_surplus_count"
				+ " FROM t_rent_class r WHERE r.room_id = ?",
				new Object[] { houseId });
	}

	/**
	 * 查找房子下的房间
	 * 
	 * @param id
	 * @return
	 */
	public Map<String, Object> findRoom(Map<String, Object> condition) throws Exception {
		Map<String, Object> querySqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_QUERY, TABLE_RENT_CLASS, null,
				condition);
		return this.findOnly((String) querySqlParam.get(JdbcUtils.SQL), (Object[]) querySqlParam.get(JdbcUtils.PARAM));
	}
	
	/**
	 * 查找房子下的房间
	 * 
	 * @param id
	 * @return
	 */
	public Map<String, Object> findRoomWithIsrent(Map<String, Object> condition) throws Exception {
		String classWithIsRent = "(select c.*, (select count(1) from t_rent_bed b where c.id = b.class_id and b.is_rent = 1) is_rent from "
				+ TABLE_RENT_CLASS + " c) with_rent_class";
		Map<String, Object> querySqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_QUERY, classWithIsRent, null,
				condition);
		return this.findOnly((String) querySqlParam.get(JdbcUtils.SQL), (Object[]) querySqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 新增房间
	 * 
	 * @param data
	 * @param condition
	 * @return
	 * @throws Exception
	 */
	public long saveRoom(Map<String, Object> data, Map<String, Object> condition) throws Exception {
		Map<String, Object> room = findRoom(condition);
		if (null == room) {
			data.put("create_date", new Date());
			Map<String, Object> insertSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_INSERT, TABLE_RENT_CLASS,
					data, null);
			return this.executeSave((String) insertSqlParam.get(JdbcUtils.SQL),
					(Object[]) insertSqlParam.get(JdbcUtils.PARAM));
		}
		return 0L;
	}

	/**
	 * 更新房间
	 * 
	 * @param data
	 * @param condition
	 * @return
	 * @throws Exception
	 */
	public boolean updateRoom(Map<String, Object> data, Map<String, Object> condition) throws Exception {
		Map<String, Object> updateSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_UPDATE, TABLE_RENT_CLASS, data,
				condition);
		return this.execute((String) updateSqlParam.get(JdbcUtils.SQL), (Object[]) updateSqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 删除房间，要求房间下的床位都没有被出租
	 * 
	 * @param roomId
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public boolean deleteRoom(int roomId) throws Exception {
		return this.execute("DELETE r FROM " + TABLE_RENT_CLASS + " r WHERE r.id = ?"
				+ " AND NOT EXISTS (SELECT 1 FROM t_rent_bed b WHERE r.id = b.class_id AND b.is_rent = 1)",
				new Object[] { roomId });
	}

	/**
	 * 查找房间下的床位
	 * 
	 * @param classId
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> findBedByClassId(int classId) throws SQLException {
		return this
				.find("SELECT rb.*, tcg.nameL grant_name FROM t_rent_bed rb LEFT JOIN t_two_card_grant tcg ON rb.grant_id = tcg.id WHERE class_id = ?",
						new Object[] { classId });
	}

	/**
	 * 查找房间下床位
	 * 
	 * @param id
	 * @return
	 */
	public Map<String, Object> findBed(Map<String, Object> condition) throws Exception {
		Map<String, Object> querySqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_QUERY, "t_rent_bed", null,
				condition);
		return this.findOnly((String) querySqlParam.get(JdbcUtils.SQL), (Object[]) querySqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 新增床位
	 * 
	 * @param data
	 * @param condition
	 * @return
	 * @throws Exception
	 */
	public long saveBed(Map<String, Object> data, Map<String, Object> condition) throws Exception {
		Map<String, Object> bed = findBed(condition);
		if (null == bed) {
			data.put("create_date", new Date());
			Map<String, Object> insertSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_INSERT, "t_rent_bed", data,
					null);
			return this.executeSave((String) insertSqlParam.get(JdbcUtils.SQL),
					(Object[]) insertSqlParam.get(JdbcUtils.PARAM));
		}
		return 0L;
	}

	/**
	 * 更新床位状态
	 * 
	 * @param data
	 * @param condition
	 * @return
	 * @throws Exception
	 */
	public boolean updateBed(Map<String, Object> data, Map<String, Object> condition) throws Exception {
		Map<String, Object> updateSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_UPDATE, "t_rent_bed", data,
				condition);
		return this.execute((String) updateSqlParam.get(JdbcUtils.SQL), (Object[]) updateSqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 删除床位，要求该床位未出租
	 * 
	 * @param bedId
	 * @param userId
	 * @return
	 * @throws SQLException
	 */
	public boolean deleteBed(int bedId, int userId) throws SQLException {
		return this.execute("DELETE b FROM t_rent_bed b WHERE b.id = ? AND b.is_rent = 0"
				+ " AND EXISTS (SELECT 1 FROM t_rent_room r WHERE r.id = b.room_id AND r.main_id = ?)", new Object[] {
				bedId, userId });
	}

	/**
	 * 查找所有小区
	 * 
	 * @param map
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws SQLException
	 */
	public Pager findMyOrder(Map<String, Object> map, int pageNo, int pageSize) throws SQLException {
		StringBuilder sql = new StringBuilder(
				"SELECT o.*, tcg.nameL grant_name FROM t_order o LEFT JOIN t_two_card_grant tcg ON o.grant_id = tcg.id WHERE 1 = 1");
		List<Object> param = new ArrayList<Object>();

		if (null != map) {
			Integer grant_id = (Integer) map.get("main_id");
			if (null != grant_id) {
				param.add(grant_id);
				sql.append(" and o.main_id = ?");
			}
		}
		sql.append(" order by o.create_date desc");
		return findPaging(sql.toString(), param.toArray(), pageNo, pageSize);
	}

	/**
	 * 更新订单状态
	 * 
	 * @param map
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws SQLException
	 */
	public boolean updateMyOrder(Map<String, Object> data, Map<String, Object> condition) throws SQLException {
		Map<String, Object> updateSqlParam = JdbcUtils.createSqlParam(JdbcUtils.SQLTYPE_UPDATE, "t_order", data,
				condition);
		return this.execute((String) updateSqlParam.get(JdbcUtils.SQL), (Object[]) updateSqlParam.get(JdbcUtils.PARAM));
	}

	/**
	 * 查房东下的房间
	 * 
	 * @param condition
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> mainRoomList(Map<String, Object> condition) throws SQLException {
		String sql = "SELECT c.name community_name, d.name dept_name, rr.* FROM t_rent_room rr, t_community c, t_department d WHERE rr.community_id = c.id AND rr.dept_id = d.id AND rr.main_id = ?";
		return this.find(sql, new Object[] { condition.get("main_id") });
	}
	
	/**
	 * 查房房客的房间
	 * 
	 * @param condition
	 * @return
	 * @throws SQLException
	 */
	public Map<String, Object> grantRoom(Map<String, Object> condition) throws SQLException {
		String sql = "SELECT c.name community_name, d.name dept_name, rr.* FROM t_rent_room rr, t_community c, t_department d WHERE rr.community_id = c.id AND rr.dept_id = d.id AND rr.id = ?";
		return this.findOnly(sql, new Object[] { condition.get("id") });
	}

	/**
	 * 查房间下的房客
	 * 
	 * @param condition
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> mainRoomGrantList(Map<String, Object> condition) throws SQLException {
		String sql = "SELECT tcg.*, rr.building, rr.unit, rr.room_num FROM t_two_card_grant tcg, t_rent_room rr WHERE tcg.rentId = rr.id AND rr.id = ?";
		return this.find(sql, new Object[] { condition.get("room_id") });
	}
}
